Database systems are used for a variety of applications. In order to accommodate large amounts of data and provide increased functionality, conventional database systems have become increasingly complex. Consequently, conventional database systems are desired to be autonomic systems. Such conventional autonomic database systems share properties such as being self-aware and being capable of adapting to varying and even unpredictable conditions.
Providing autonomic capabilities in database systems is typically achieved by incorporating subsystems, such as conventional wizards and optimizers, into the database systems. For example conventional autonomic systems may include wizards and/or optimizers for evaluating performance of the conventional database system and adapting to improve performance by generating and/or implementing changes to the conventional database system. These wizards and/or optimizers may assist in setting the initial settings for the conventional database system and/or assist in updating or tuning settings in an autonomic conventional database system. Such wizards thus configure a variety of settings, or parameters, that pertain to items such as server agents, input/output subagents, logging, sorting, and other operations and components of the database system. Moreover, the settings may include a set of indexes and materialized query tables (MQTs). These settings are determined based on characteristics of the database systems such as total memory available, the number of disks, the number of CPUs, as well as other information, some of which may be user-supplied. Together, these settings are termed the configuration of the database systems. Thus, conventional database systems may use wizards and/or optimizers for providing the configuration of the conventional database system.
For example, FIG. 1 depicts a conventional method 10 for determining the settings for a conventional database system. The database system is monitored, typically by obtaining a snapshot of the conventional database system, via step 12. A snapshot is effectively the status of the conventional database system at a particular instant in time. Thus, the snapshot represents the status of the database system at a particular point in time. For example, the snapshot may be the database workloads at a particular time. Thus, the work load, the CPU usage, memory space or consumption for use in caching and sorting, buffer pool activity, logging behavior, disk space, the behavior of existing objects, and/or other characteristics of the conventional database system may be obtained in the snapshot.
The snapshot is analyzed in order to determine a desired configuration, via step 14. Thus, the status of the conventional database system in the snapshot is used to determine settings that may improve or maintain certain aspects of the performance of the conventional database system. If the conventional database system is autonomic, the analysis may be performed by a conventional wizard and/or optimizer. In some conventional database systems, the analysis may include a cost/benefit analysis to determine whether the cost of implementing a particular configuration is warranted for the benefits expected to be achieved.
At least one configuration is provided based on the analysis, via step 16. Greater than one configuration might be provided to allow a system administrator or other authorized user to select between the configurations to implement the configuration they believe is optimal. The authorized user may also alter some of the settings on a particular configuration provided. A selected configuration is committed, via step 18. Typically, the system administrator selects the configuration to be committed at least for the initial configuration. However, particularly for an autonomic conventional database system, the system might select the configuration to be committed. Thus, the selected configuration is implemented for the conventional database system to use.
Once the selected configuration is committed, the database is allowed to run using the settings in the committed configuration, via step 20. For many conventional database systems, once the settings are initially committed, the conventional database system is simply allowed to run with static settings. However, steps 12, 14, 16, 18, and 20 may optionally be repeated, via step 22. For example, in an autonomic conventional database system, the steps 12, 14, 16, 18, and 20 may be repeated periodically in order to adjust the configuration to account for changes in the conventional database system.
Although the conventional method 10 allows for the configuration of a conventional database system to be updated, one of ordinary skill in the art will readily recognize that there may be significant drawbacks to the use of the conventional method 10. In particular, the analysis performed in step 14 is based upon a snapshot of the status of the database system. The characteristics of the conventional database system, such as work flow, memory consumption, or other characteristics change over time. In particular, in “real time” warehouse environments, the complexity and diversity of user workloads is enormous. Static snapshots may not be sufficient in these dynamic environments because snapshots are limited by the scope of their input stream at time t(n). In addition, conventional wizard that use such static snapshots generally operate under resource constraints, such as finite time and disk space. As a result, it is entirely possible to formulate sub-optimal performance strategies because lots of possibilities exist in the solution's search space. In most cases, the conventional wizard's optimal solution must converge in a relatively short time which compounds the problem. Thus, the new configuration committed in step 18 may already be out of date or may not be appropriate for future usage of the conventional database system. Moreover, the configurations are typically quite complex. As a result, the new configuration may have unintended consequences. Even if the new configuration improves performance in a particular area, its consequences on another aspect of the performance of the conventional database system may be disastrous. Moreover, building the new configuration, for example forming MQTs may consume a significant amount of resources. It may be difficult to justify the additional resource when the effects of the new configuration are only surmised based on one or more snapshots.
Accordingly, what is needed is an improved method and system for updating the performance of a database system. The present invention addresses such a need.